package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.DBException;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.joda.time.DateTime;

public class Mhrmeducation extends X_hrm_education {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 4552076759788735458L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmeducation.class);

	public Mhrmeducation(Properties ctx) {
		super(ctx);
	}

	public Mhrmeducation(Properties ctx, int hrm_education_ID, String trxName) {
		super(ctx, hrm_education_ID, trxName);
	}

	public Mhrmeducation(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}

	/**
	 * 	Get Education Lines of Payroll Employee
	 * 	@param whereClause starting with AND
	 * 	@return lines
	 */
	public Mhrmeducation[] getLinesEducation (int block_id, int department_id)
	{
		
		Mhrmeducation hrmedu = new Mhrmeducation(getCtx(),0,get_TrxName());
		
		String sql_ = "SELECT hrm_education.ad_client_id, \n"+
								"hrm_education.ad_org_id, \n"+
								"hrm_education.created, \n"+
								"hrm_education.createdby, \n"+
								"hrm_education.hrm_certificate, \n"+
								"hrm_education.hrm_diplomano, \n"+
								"hrm_education.hrm_diplomaqualification, \n"+
								"hrm_education.hrm_diplomaspeciality, \n"+
								"hrm_education.hrm_education_id, \n"+
								"COALESCE(hrm_education.hrm_educationtype,'0') as hrm_educationtype, \n"+
								"hrm_education.hrm_institutionname, \n"+
								"hrm_education.isactive, \n"+
								"hrm_education.updated, \n"+
								"hrm_education.updatedby, \n"+
								"c_bpartner.c_bpartner_id, \n"+
								"hrm_education.hrm_certificatecompany, \n"+
								"hrm_education.hrm_academicdate, \n"+
								"hrm_education.processed, \n"+
								"hrm_education.hrm_finishyear, \n"+
								"hrm_education.hrm_startyear, \n"+
								"hrm_education.hrm_academictype_id, \n" +
								"hrm_education.hrm_chitno, \n" +
								"hrm_education.description, \n" +
								"hrm_education.hrm_crtificateno \n" +
				"FROM c_bpartner \n"+
				"LEFT JOIN hr_employee ON hr_employee.c_bpartner_id=c_bpartner.c_bpartner_id " +
				"AND hr_employee.enddate = (select max(tt.enddate) from hr_employee tt where tt.c_bpartner_id=hr_employee.c_bpartner_id) \n"+
				"LEFT JOIN hrm_education ON hrm_education.c_bpartner_id=hr_employee.c_bpartner_id AND hrm_education.ad_org_id=? \n" +
				"WHERE hr_employee.ad_client_id=? \n" +
				"AND hr_employee.ad_org_id=? \n" +
				"AND c_bpartner.isEmployee='Y' and c_bpartner.isActive='Y' \n";
		
		StringBuffer sql = new StringBuffer(sql_);
		
		List<Object> param = new ArrayList<Object>();
		param.add(new BigDecimal(getAD_Org_ID()));
		param.add(new BigDecimal(getAD_Client_ID()));
		param.add(new BigDecimal(getAD_Org_ID()));
		
		if (department_id != 0){
			sql.append(" AND hr_employee.hr_department_id=? \n");
			param.add(department_id);
		}
		else if(block_id !=0){
			sql.append(" AND hr_employee.hr_department_id IN (SELECT node_id FROM ad_treenodedep WHERE parent_id=?) \n");
			param.add(block_id);
			
		}
		
		sql.append(" ORDER BY hr_employee.hr_department_id, c_bpartner.c_bpartner_id");
		
		List<Mhrmeducation> list = new ArrayList<Mhrmeducation>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement (sql.toString(), get_TrxName());
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();
			while (rs.next ())
			{
				hrmedu = new Mhrmeducation(getCtx(), rs, get_TrxName());
				list.add(hrmedu);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
			throw new DBException(e, sql.toString());
		} finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return list.toArray(new Mhrmeducation[list.size()]);
		
	}	//	getLines
	
	/**
	 * Get Education Lines of Business Partner
	 * @param C_BPartner_ID
	 * @param educationType
	 * @return
	 */
	public static Mhrmeducation[] getEducationBPartner(int C_BPartner_ID, Timestamp startdate, Timestamp enddate, String educationType) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmeducation hrmedu = null;
			
		ArrayList<Mhrmeducation> list = new ArrayList<Mhrmeducation>();
		List<Object> param = new ArrayList<Object>();
		param.add(Env.getAD_Client_ID(Env.getCtx()));
		param.add(C_BPartner_ID);
		
		// 
		String sql_ = "SELECT * FROM "+I_hrm_education.Table_Name+" T \n" +
				"WHERE T.AD_CLIENT_ID=? \n" +
				"AND T.C_BPARTNER_ID=? \n";
		
		if(startdate != null && enddate == null)
			enddate = new Timestamp(new DateTime().getMillis());
		
		if(educationType != null && !educationType.isEmpty()){
			sql_ += " AND T.HRM_EDUCATIONTYPE = '" + educationType+"'";
			
			if(startdate != null && enddate != null){
				sql_ += " AND T.HRM_ACADEMICDATE BETWEEN ? AND ? \n";
				param.add(startdate);
				param.add(enddate);
			}
		}
		
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();	
			while(rs.next()){
				hrmedu = new  Mhrmeducation(Env.getCtx(), rs, null);
				list.add(hrmedu);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new Mhrmeducation[list.size()]);
	}

}
